package com.bolt.support.query.toolkit;

import com.bolt.common.utils.StrUtil;

import java.util.regex.Pattern;

public class DialectUtil {

    // union 匹配模式
    public static final Pattern UNION_PATTERN = Pattern.compile("(?i)\\W+union\\W+");

    public static final Pattern ORDER_BY_PATTERN = Pattern.compile("(?i)\\Worder\\s+by\\W");

    public static final Pattern GROUP_BY_PATTERN = Pattern.compile("(?i)\\Wgroup\\s+by\\W");

    // distinct 匹配模式
    public static final Pattern DISTINCT_PATTERN = Pattern.compile("(?i)^select\\s+distinct\\s+");

    /**
     * 统计正则表达式
     */
    public static final Pattern STAT_PATTERN = Pattern
            .compile("\\W(sum|avg|min|max|first|last|first_value|last_value)\\(");

    /**
     * 查询select 匹配
     */
    private static final String SELECT_REGEX = "select\\s+";

    /**
     * 查询from 匹配
     */
    private static final String FROM_REGEX = "\\s+from[\\(\\s+]";

    private static final String WHERE_REGEX = "\\s+where[\\(\\s+]";


    /**
     * @param sql
     * @return
     * @todo 去除掉sql中的所有对称的括号中的内容，排除干扰
     */
    public static String clearDisturbSql(String sql) {
        StringBuilder lastSql = new StringBuilder(sql);
        // 找到第一个select 所对称的from位置，排除掉子查询中的内容
        int fromIndex = StrUtil.getMatchPatternIndex(sql.toLowerCase(), SELECT_REGEX, FROM_REGEX, 0);
        if (fromIndex != -1) {
            lastSql.delete(0, fromIndex);
        }
        // 删除所有对称的括号中的内容
        int start = lastSql.indexOf("(");
        int symMarkEnd;
        while (start != -1) {
            symMarkEnd = StrUtil.getMarkIndex("(", ")", lastSql.toString(), start);
            if (symMarkEnd != -1) {
                lastSql.delete(start, symMarkEnd + 1);
                start = lastSql.indexOf("(");
            } else {
                break;
            }
        }
        return lastSql.toString();
    }

    /**
     * 除掉sql中的所有对称的select 和 from 中的内容，排除干扰
     *
     * @param sql
     * @return
     */
    private static String clearSymSelectFromSql(String sql) {
        // 先转化为小写
        String realSql = sql.toLowerCase();
        StringBuilder lastSql = new StringBuilder(realSql);
        String SELECT_REGEX = "\\Wselect\\s+";
        String FROM_REGEX = "\\sfrom[\\(|\\s+]";

        // 删除所有对称的括号中的内容
        int start = StrUtil.matchIndex(realSql, SELECT_REGEX);
        int symMarkEnd;
        while (start != -1) {
            symMarkEnd = StrUtil.getMatchPatternIndex(lastSql.toString(), SELECT_REGEX, FROM_REGEX, start);
            if (symMarkEnd != -1) {
                lastSql.delete(start + 1, symMarkEnd + 5);
                start = StrUtil.matchIndex(lastSql.toString(), SELECT_REGEX);
            } else {
                break;
            }
        }
        return lastSql.toString();
    }


    public static String getCountSql(String sql) {
        String query_tmp = sql;
        int lastBracketIndex = query_tmp.lastIndexOf(")");
        int sql_from_index = 0;
        // sql不以from开头，截取from 后的部分语句
        if (StrUtil.matchIndex(query_tmp, "from") != 0) {
            sql_from_index = StrUtil.getMatchPatternIndex(query_tmp.toLowerCase(), SELECT_REGEX, FROM_REGEX, 0);
        }
        // 剔除order提高运行效率
        int orderByIndex = StrUtil.matchLastIndex(query_tmp, ORDER_BY_PATTERN);
        // order by 在from 之后
        if (orderByIndex > sql_from_index) {
            // 剔除order by 语句
            if (orderByIndex > lastBracketIndex) {
                query_tmp = query_tmp.substring(0, orderByIndex + 1);
            } else {
                // 剔除掉order by 后面语句对称的() 内容
                String orderJudgeSql = clearDisturbSql(query_tmp.substring(orderByIndex + 1));
                // 在order by 不在子查询内,说明可以整体切除掉order by
                if (orderJudgeSql.indexOf(")") == -1) {
                    query_tmp = query_tmp.substring(0, orderByIndex + 1);
                }
            }
        }

        int groupIndex = StrUtil.matchLastIndex(query_tmp, GROUP_BY_PATTERN);
        // 判断group by 是否是内层，如select * from (select * from table group by)
        boolean isInnerGroup = false;
        if (groupIndex != -1) {
            isInnerGroup = clearDisturbSql(query_tmp.substring(groupIndex + 1)).lastIndexOf(")") != -1;
        }
        final StringBuilder countQueryStr = new StringBuilder();
        boolean hasUnion = StrUtil.matches(query_tmp, UNION_PATTERN);
        // 不包含distinct和group by 等,则剔除[select * ] from 变成select count(1) from
        // 性能最优
        if (!StrUtil.matches(query_tmp.trim(), DISTINCT_PATTERN) && !hasUnion
                && (groupIndex == -1 || (groupIndex < lastBracketIndex && isInnerGroup))) {
            int selectIndex = StrUtil.matchIndex(query_tmp.toLowerCase(), SELECT_REGEX);
            // 截取出select 和from之间的语句
            String selectFields = (sql_from_index < 1) ? ""
                    : query_tmp.substring(selectIndex + 6, sql_from_index).toLowerCase();
            // 剔除嵌套的子查询语句中select 和 from 之间的内容,便于判断统计函数的作用位置
            selectFields = clearSymSelectFromSql(selectFields);
            // 存在统计函数 update by chenrenfei ,date: 2017-2-24
            if (StrUtil.matches(selectFields, STAT_PATTERN)) {
                countQueryStr.append("select count(1) from (").append(query_tmp).append(") sag_count_tmpTable ");
            } else {
                // 截取from后的部分
                countQueryStr.append("select count(1) ")
                        .append((sql_from_index != -1 ? query_tmp.substring(sql_from_index) : query_tmp));
            }
        } // 包含distinct 或包含union则直接将查询作为子表(普通做法)
        else {
            countQueryStr.append("select count(1) from (").append(query_tmp).append(") sag_count_tmpTable ");
        }
        return countQueryStr.toString();
    }

}

 